{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AdventureWorks - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**For every customer with a 'Main Office' in Dallas show AddressLine1 of the 'Main Office' and AddressLine1 of the 'Shipping' address - if there is no shipping address leave it blank. Use one row per customer.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>companyname</th>\n",
       "        <th>main_office</th>\n",
       "        <th>shipping</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elite Bikes</td>\n",
       "        <td>Po Box 8259024</td>\n",
       "        <td>9178 Jumping St.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rental Bikes</td>\n",
       "        <td>99828 Routh Street, Suite 825</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Third Bike Store</td>\n",
       "        <td>2500 North Stemmons Freeway</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Town Industries</td>\n",
       "        <td>P.O. Box 6256916</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Unsurpassed Bikes</td>\n",
       "        <td>Po Box 8035996</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Elite Bikes', 'Po Box 8259024', '9178 Jumping St.'),\n",
       " ('Rental Bikes', '99828 Routh Street, Suite 825', ''),\n",
       " ('Third Bike Store', '2500 North Stemmons Freeway', ''),\n",
       " ('Town Industries', 'P.O. Box 6256916', ''),\n",
       " ('Unsurpassed Bikes', 'Po Box 8035996', '')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT CustomerAW.CustomerID FROM\n",
    "      CustomerAW JOIN CustomerAddress ON (\n",
    "          CustomerAW.CustomerID=CustomerAddress.CustomerID) JOIN\n",
    "        Address ON (CustomerAddress.AddressID=Address.AddressID)\n",
    "        WHERE Address.City='Dallas' AND\n",
    "            CustomerAddress.AddressType='Main Office'\n",
    ")\n",
    "SELECT CustomerAW.CompanyName,\n",
    "  MAX(CASE WHEN AddressType='Main Office' THEN AddressLine1 ELSE '' END) main_office,\n",
    "    MAX(CASE WHEN AddressType='Shipping' THEN AddressLine1 ELSE '' END) shipping\n",
    "    FROM CustomerAW JOIN CustomerAddress ON (\n",
    "          CustomerAW.CustomerID=CustomerAddress.CustomerID) JOIN\n",
    "        Address ON (CustomerAddress.AddressID=Address.AddressID)\n",
    "        WHERE CustomerAW.CustomerID IN (SELECT CustomerID FROM t)\n",
    "        GROUP BY CustomerAW.CompanyName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**For each order show the SalesOrderID and SubTotal calculated three ways:**\n",
    "\n",
    "- **A) From the SalesOrderHeader**\n",
    "- **B) Sum of OrderQty*UnitPrice**\n",
    "- **C) Sum of OrderQty*ListPrice**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>salesorderid</th>\n",
       "        <th>subt_a</th>\n",
       "        <th>subt_b</th>\n",
       "        <th>subt_c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71774</td>\n",
       "        <td>880.35</td>\n",
       "        <td>713.8</td>\n",
       "        <td>1189.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71776</td>\n",
       "        <td>78.81</td>\n",
       "        <td>63.9</td>\n",
       "        <td>106.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71780</td>\n",
       "        <td>38418.69</td>\n",
       "        <td>29922.81</td>\n",
       "        <td>56651.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71782</td>\n",
       "        <td>39785.33</td>\n",
       "        <td>33319.68</td>\n",
       "        <td>55533.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71783</td>\n",
       "        <td>83858.43</td>\n",
       "        <td>65682.74</td>\n",
       "        <td>121625.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71784</td>\n",
       "        <td>108561.83</td>\n",
       "        <td>89868.88</td>\n",
       "        <td>151932.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71796</td>\n",
       "        <td>57634.63</td>\n",
       "        <td>47848.02</td>\n",
       "        <td>79746.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71797</td>\n",
       "        <td>78029.69</td>\n",
       "        <td>65122.79</td>\n",
       "        <td>108986.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71815</td>\n",
       "        <td>1141.58</td>\n",
       "        <td>926.91</td>\n",
       "        <td>1544.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71816</td>\n",
       "        <td>3398.17</td>\n",
       "        <td>2847.37</td>\n",
       "        <td>4745.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71831</td>\n",
       "        <td>2016.34</td>\n",
       "        <td>1712.91</td>\n",
       "        <td>2854.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71832</td>\n",
       "        <td>35775.21</td>\n",
       "        <td>28950.48</td>\n",
       "        <td>50559.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71845</td>\n",
       "        <td>41622.05</td>\n",
       "        <td>34118.3</td>\n",
       "        <td>57768.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71846</td>\n",
       "        <td>2453.76</td>\n",
       "        <td>1884.38</td>\n",
       "        <td>3592.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71856</td>\n",
       "        <td>602.19</td>\n",
       "        <td>500.3</td>\n",
       "        <td>833.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71858</td>\n",
       "        <td>13823.71</td>\n",
       "        <td>11528.8</td>\n",
       "        <td>19214.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71863</td>\n",
       "        <td>3324.28</td>\n",
       "        <td>2777.05</td>\n",
       "        <td>4633.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71867</td>\n",
       "        <td>1059.31</td>\n",
       "        <td>858.9</td>\n",
       "        <td>1431.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71885</td>\n",
       "        <td>550.39</td>\n",
       "        <td>524.64</td>\n",
       "        <td>874.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71895</td>\n",
       "        <td>246.74</td>\n",
       "        <td>221.24</td>\n",
       "        <td>368.76</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(71774, 880.35, 713.8, 1189.66),\n",
       " (71776, 78.81, 63.9, 106.5),\n",
       " (71780, 38418.69, 29922.81, 56651.56),\n",
       " (71782, 39785.33, 33319.68, 55533.31),\n",
       " (71783, 83858.43, 65682.74, 121625.43),\n",
       " (71784, 108561.83, 89868.88, 151932.58),\n",
       " (71796, 57634.63, 47848.02, 79746.71),\n",
       " (71797, 78029.69, 65122.79, 108986.4),\n",
       " (71815, 1141.58, 926.91, 1544.86),\n",
       " (71816, 3398.17, 2847.37, 4745.68),\n",
       " (71831, 2016.34, 1712.91, 2854.91),\n",
       " (71832, 35775.21, 28950.48, 50559.01),\n",
       " (71845, 41622.05, 34118.3, 57768.21),\n",
       " (71846, 2453.76, 1884.38, 3592.65),\n",
       " (71856, 602.19, 500.3, 833.84),\n",
       " (71858, 13823.71, 11528.8, 19214.74),\n",
       " (71863, 3324.28, 2777.05, 4633.78),\n",
       " (71867, 1059.31, 858.9, 1431.5),\n",
       " (71885, 550.39, 524.64, 874.44),\n",
       " (71895, 246.74, 221.24, 368.76),\n",
       " (71897, 12685.89, 10585.01, 17641.75),\n",
       " (71898, 63980.99, 53248.57, 88747.82),\n",
       " (71899, 2415.67, 1856.18, 3545.67),\n",
       " (71902, 74058.81, 59893.72, 106151.57),\n",
       " (71915, 2137.23, 1732.86, 2888.15),\n",
       " (71917, 40.9, 37.73, 62.93),\n",
       " (71920, 2980.79, 2527.08, 4211.88),\n",
       " (71923, 106.54, 96.03, 166.81),\n",
       " (71935, 6634.3, 5533.78, 9229.27),\n",
       " (71936, 98278.69, 79589.08, 138124.87),\n",
       " (71938, 88812.86, 5102.95, 8504.95)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH a AS (\n",
    "    SELECT SalesOrderID, SubTotal subtotal_a FROM SalesOrderHeader\n",
    "), b AS (\n",
    "    SELECT SalesOrderID, SUM(OrderQty*UnitPrice*(1-UnitPriceDiscount)) subtotal_b\n",
    "    FROM SalesOrderDetail\n",
    "    GROUP BY SalesOrderID\n",
    "), c AS (\n",
    "    SELECT SalesOrderID, SUM(OrderQty*ListPrice) subtotal_c\n",
    "    FROM SalesOrderDetail JOIN Product ON (\n",
    "        SalesOrderDetail.ProductID=Product.ProductID)\n",
    "    GROUP BY SalesOrderID\n",
    ")\n",
    "SELECT a.SalesOrderID, ROUND(subtotal_a, 2) subt_a, \n",
    "    ROUND(subtotal_b, 2) subt_b, ROUND(subtotal_c, 2) subt_c\n",
    "    FROM a JOIN b ON (a.SalesOrderID=b.SalesOrderID) JOIN\n",
    "    c ON (a.SalesOrderID=c.SalesOrderID)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**Show the best selling item by value.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>subtotal</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Blue, 60</td>\n",
       "        <td>37191.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Black, 42</td>\n",
       "        <td>37178.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Black, 38</td>\n",
       "        <td>35801.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-350-W Yellow, 48</td>\n",
       "        <td>33509.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Yellow, 60</td>\n",
       "        <td>23745.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Blue, 50</td>\n",
       "        <td>22887.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Silver, 42</td>\n",
       "        <td>20879.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-350-W Yellow, 40</td>\n",
       "        <td>20411.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Black, 46</td>\n",
       "        <td>19277.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Silver, 46</td>\n",
       "        <td>18095.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Blue, 46</td>\n",
       "        <td>17165.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Yellow, 46</td>\n",
       "        <td>17165.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-350-W Yellow, 42</td>\n",
       "        <td>15887.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Silver, 38</td>\n",
       "        <td>15311.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-250 Black, 44</td>\n",
       "        <td>13194.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-2000 Blue, 54</td>\n",
       "        <td>13120.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HL Mountain Frame - Silver, 38</td>\n",
       "        <td>13099.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-3000 Blue, 50</td>\n",
       "        <td>10244.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HL Mountain Frame - Black, 42</td>\n",
       "        <td>9717.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-550-W Yellow, 38</td>\n",
       "        <td>9412.06</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">137 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Touring-1000 Blue, 60', 37191.44),\n",
       " ('Mountain-200 Black, 42', 37178.73),\n",
       " ('Mountain-200 Black, 38', 35801.74),\n",
       " ('Road-350-W Yellow, 48', 33509.58),\n",
       " ('Touring-1000 Yellow, 60', 23745.32),\n",
       " ('Touring-1000 Blue, 50', 22887.04),\n",
       " ('Mountain-200 Silver, 42', 20879.85),\n",
       " ('Road-350-W Yellow, 40', 20411.8),\n",
       " ('Mountain-200 Black, 46', 19277.86),\n",
       " ('Mountain-200 Silver, 46', 18095.87),\n",
       " ('Touring-1000 Blue, 46', 17165.28),\n",
       " ('Touring-1000 Yellow, 46', 17165.28),\n",
       " ('Road-350-W Yellow, 42', 15887.18),\n",
       " ('Mountain-200 Silver, 38', 15311.89),\n",
       " ('Road-250 Black, 44', 13194.09),\n",
       " ('Touring-2000 Blue, 54', 13120.38),\n",
       " ('HL Mountain Frame - Silver, 38', 13099.2),\n",
       " ('Touring-3000 Blue, 50', 10244.43),\n",
       " ('HL Mountain Frame - Black, 42', 9717.12),\n",
       " ('Road-550-W Yellow, 38', 9412.06),\n",
       " ('Touring-3000 Yellow, 62', 8744.85),\n",
       " ('Touring-1000 Blue, 54', 8582.64),\n",
       " ('HL Mountain Frame - Silver, 46', 8187.0),\n",
       " ('Touring-3000 Yellow, 44', 8017.38),\n",
       " ('Road-250 Black, 48', 7330.05),\n",
       " ('Touring-2000 Blue, 60', 7289.1),\n",
       " ('HL Touring Frame - Yellow, 60', 7228.2),\n",
       " ('Touring-1000 Yellow, 50', 7152.2),\n",
       " ('Touring-3000 Blue, 54', 6681.15),\n",
       " ('Touring-2000 Blue, 50', 6560.19),\n",
       " ('Touring-2000 Blue, 46', 6560.19),\n",
       " ('Touring-1000 Yellow, 54', 5721.76),\n",
       " ('HL Mountain Frame - Black, 38', 5668.32),\n",
       " ('Road-750 Black, 48', 5183.84),\n",
       " ('HL Road Frame - Black, 44', 5153.4),\n",
       " ('HL Mountain Frame - Silver, 42', 4912.2),\n",
       " ('ML Road Frame-W - Yellow, 48', 4282.8),\n",
       " ('HL Touring Frame - Blue, 54', 4216.45),\n",
       " ('Mountain-400-W Silver, 46', 4155.21),\n",
       " ('Mountain-400-W Silver, 40', 4155.21),\n",
       " ('Touring-3000 Yellow, 50', 4008.69),\n",
       " ('Road-750 Black, 52', 3887.88),\n",
       " ('Mountain-500 Black, 48', 3887.88),\n",
       " ('ML Road Frame-W - Yellow, 44', 3569.0),\n",
       " ('Road-750 Black, 58', 3563.89),\n",
       " ('HL Touring Frame - Yellow, 54', 3011.75),\n",
       " ('Classic Vest, S', 2968.93),\n",
       " ('Road-250 Black, 52', 2932.02),\n",
       " ('HL Crankset', 2915.88),\n",
       " ('Touring-3000 Blue, 58', 2672.46),\n",
       " ('Touring-3000 Yellow, 54', 2672.46),\n",
       " ('ML Mountain Frame-W - Silver, 40', 2621.4),\n",
       " ('HL Road Frame - Red, 62', 2576.7),\n",
       " ('HL Road Frame - Red, 44', 2576.7),\n",
       " ('ML Mountain Frame-W - Silver, 42', 2402.95),\n",
       " ('Mountain-400-W Silver, 42', 2308.45),\n",
       " ('Touring-3000 Blue, 44', 2227.05),\n",
       " ('Hitch Rack - 4-Bike', 2088.0),\n",
       " ('Road-550-W Yellow, 40', 2016.87),\n",
       " ('Road-550-W Yellow, 48', 2016.87),\n",
       " (\"Women's Mountain Shorts, S\", 1977.72),\n",
       " ('Mountain-500 Silver, 52', 1921.0),\n",
       " ('Mountain-400-W Silver, 38', 1846.76),\n",
       " ('LL Road Frame - Black, 58', 1820.97),\n",
       " ('HL Touring Frame - Blue, 60', 1807.05),\n",
       " ('LL Mountain Frame - Black, 44', 1798.44),\n",
       " ('Short-Sleeve Classic Jersey, XL', 1643.78),\n",
       " ('Mountain-500 Black, 44', 1619.95),\n",
       " ('LL Road Frame - Black, 52', 1618.64),\n",
       " ('Short-Sleeve Classic Jersey, L', 1499.64),\n",
       " ('ML Road Frame-W - Yellow, 38', 1427.6),\n",
       " ('LL Mountain Frame - Silver, 44', 1425.87),\n",
       " ('LL Mountain Frame - Silver, 40', 1425.87),\n",
       " ('Hydration Pack - 70 oz.', 1418.57),\n",
       " ('Long-Sleeve Logo Jersey, L', 1397.02),\n",
       " ('Mountain-500 Silver, 42', 1356.0),\n",
       " ('LL Mountain Frame - Black, 42', 1348.83),\n",
       " ('Touring-3000 Blue, 62', 1336.23),\n",
       " ('Touring-3000 Yellow, 58', 1336.23),\n",
       " ('LL Mountain Frame - Silver, 42', 1267.44),\n",
       " ('Mountain-500 Silver, 40', 1243.0),\n",
       " (\"Women's Mountain Shorts, L\", 1133.73),\n",
       " ('Classic Vest, M', 1104.9),\n",
       " ('HL Bottom Bracket', 1093.35),\n",
       " ('LL Crankset', 1052.9),\n",
       " ('Mountain-500 Black, 40', 971.97),\n",
       " ('Mountain-500 Black, 42', 971.97),\n",
       " ('Mountain-500 Black, 52', 971.97),\n",
       " ('Short-Sleeve Classic Jersey, S', 971.7),\n",
       " ('Sport-100 Helmet, Black', 941.04),\n",
       " ('Front Brakes', 766.8),\n",
       " ('Long-Sleeve Logo Jersey, M', 719.76),\n",
       " ('Front Derailleur', 713.57),\n",
       " ('Sport-100 Helmet, Blue', 708.4),\n",
       " ('Mountain-500 Silver, 48', 678.0),\n",
       " ('Road-550-W Yellow, 42', 672.29),\n",
       " ('Sport-100 Helmet, Red', 671.68),\n",
       " ('HL Touring Frame - Blue, 50', 602.35),\n",
       " ('LL Touring Frame - Yellow, 44', 600.15),\n",
       " ('LL Touring Frame - Blue, 54', 600.15),\n",
       " ('LL Mountain Frame - Black, 48', 599.48),\n",
       " ('HL Mountain Pedal', 583.08),\n",
       " ('Rear Derailleur', 583.04),\n",
       " ('ML Road Pedal', 484.25),\n",
       " ('LL Mountain Frame - Silver, 52', 475.29),\n",
       " ('Mountain-500 Silver, 44', 452.0),\n",
       " ('ML Mountain Handlebars', 445.8),\n",
       " ('HL Road Pedal', 437.31),\n",
       " ('Long-Sleeve Logo Jersey, XL', 419.86),\n",
       " ('LL Touring Frame - Blue, 50', 400.1),\n",
       " ('LL Touring Frame - Yellow, 50', 400.1),\n",
       " ('LL Road Pedal', 388.64),\n",
       " ('ML Mountain Seat/Saddle', 375.68),\n",
       " ('Half-Finger Gloves, M', 352.56),\n",
       " ('LL Mountain Pedal', 291.48),\n",
       " ('Half-Finger Gloves, L', 279.11),\n",
       " ('AWC Logo Cap', 272.91),\n",
       " ('Racing Socks, L', 257.27),\n",
       " ('Bike Wash - Dissolver', 226.93),\n",
       " ('LL Bottom Bracket', 226.73),\n",
       " ('ML Mountain Pedal', 223.5),\n",
       " ('HL Mountain Seat/Saddle', 221.06),\n",
       " ('HL Touring Seat/Saddle', 221.06),\n",
       " ('HL Road Handlebars', 216.48),\n",
       " ('HL Mountain Handlebars', 216.48),\n",
       " (\"Women's Mountain Shorts, M\", 209.95),\n",
       " ('LL Touring Frame - Yellow, 62', 200.05),\n",
       " ('LL Mountain Handlebars', 187.04),\n",
       " ('Half-Finger Gloves, S', 176.28),\n",
       " ('Water Bottle - 30 oz.', 135.34),\n",
       " ('LL Mountain Seat/Saddle', 97.62),\n",
       " ('Chain', 97.12),\n",
       " ('Rear Brakes', 63.9),\n",
       " ('HL Road Seat/Saddle', 63.16),\n",
       " ('Racing Socks, M', 59.29),\n",
       " ('HL Touring Handlebars', 54.94),\n",
       " ('Patch Kit/8 Patches', 27.4)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Product.Name, ROUND(SUM(OrderQty*UnitPrice), 2) subtotal\n",
    "    FROM SalesOrderDetail JOIN Product ON (\n",
    "        SalesOrderDetail.ProductID=Product.ProductID)\n",
    "    GROUP BY Product.Name\n",
    "    ORDER BY subtotal DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Show how many orders are in the following ranges (in $):**\n",
    "\n",
    "```\n",
    "    RANGE      Num Orders      Total Value\n",
    "    0-  99\n",
    "  100- 999\n",
    " 1000-9999\n",
    "10000-\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>range</th>\n",
       "        <th>numorders</th>\n",
       "        <th>totalvalue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;0-  99</td>\n",
       "        <td>3</td>\n",
       "        <td>158.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;100- 999</td>\n",
       "        <td>5</td>\n",
       "        <td>2386.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td> 1000-9999</td>\n",
       "        <td>10</td>\n",
       "        <td>27561.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10000-    </td>\n",
       "        <td>14</td>\n",
       "        <td>835326.81</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('    0-  99', 3, 158.66),\n",
       " ('  100- 999', 5, 2386.21),\n",
       " (' 1000-9999', 10, 27561.43),\n",
       " ('10000-    ', 14, 835326.81)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS ( -- generate the RANGE column\n",
    "    SELECT SubTotal, SalesOrderID,\n",
    "      CASE WHEN SubTotal BETWEEN 0 AND 99.99      THEN '    0-  99'\n",
    "           WHEN SubTotal BETWEEN 100 AND 999.99   THEN '  100- 999'\n",
    "           WHEN SubTotal BETWEEN 1000 AND 9999.99 THEN ' 1000-9999'\n",
    "           ELSE                                          '10000-    '\n",
    "      END RANGE\n",
    "    FROM SalesOrderHeader\n",
    ")\n",
    "SELECT RANGE, COUNT(*) NumOrders, \n",
    "    ROUND(SUM(SubTotal), 2) TotalValue FROM t\n",
    "    GROUP BY RANGE\n",
    "    ORDER BY RANGE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Identify the three most important cities. Show the break down of top level product category against city.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>category</th>\n",
       "        <th>amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Bottom Brackets</td>\n",
       "        <td>388.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Brakes</td>\n",
       "        <td>255.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Chains</td>\n",
       "        <td>36.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Cranksets</td>\n",
       "        <td>1773.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Derailleurs</td>\n",
       "        <td>638.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Gloves</td>\n",
       "        <td>88.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Handlebars</td>\n",
       "        <td>292.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Helmets</td>\n",
       "        <td>20.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Mountain Bikes</td>\n",
       "        <td>50881.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Mountain Frames</td>\n",
       "        <td>24018.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Pedals</td>\n",
       "        <td>390.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Road Bikes</td>\n",
       "        <td>5102.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Saddles</td>\n",
       "        <td>284.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Shorts</td>\n",
       "        <td>1072.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Bike Racks</td>\n",
       "        <td>144.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Bottles and Cages</td>\n",
       "        <td>29.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Caps</td>\n",
       "        <td>57.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Cleaners</td>\n",
       "        <td>38.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Gloves</td>\n",
       "        <td>264.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Handlebars</td>\n",
       "        <td>72.16</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">42 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('London', 'Bottom Brackets', 388.73),\n",
       " ('London', 'Brakes', 255.6),\n",
       " ('London', 'Chains', 36.42),\n",
       " ('London', 'Cranksets', 1773.81),\n",
       " ('London', 'Derailleurs', 638.85),\n",
       " ('London', 'Gloves', 88.14),\n",
       " ('London', 'Handlebars', 292.63),\n",
       " ('London', 'Helmets', 20.99),\n",
       " ('London', 'Mountain Bikes', 50881.99),\n",
       " ('London', 'Mountain Frames', 24018.8),\n",
       " ('London', 'Pedals', 390.32),\n",
       " ('London', 'Road Bikes', 5102.95),\n",
       " ('London', 'Saddles', 284.43),\n",
       " ('London', 'Shorts', 1072.14),\n",
       " ('Union City', 'Bike Racks', 144.0),\n",
       " ('Union City', 'Bottles and Cages', 29.9),\n",
       " ('Union City', 'Caps', 57.31),\n",
       " ('Union City', 'Cleaners', 38.16),\n",
       " ('Union City', 'Gloves', 264.42),\n",
       " ('Union City', 'Handlebars', 72.16),\n",
       " ('Union City', 'Helmets', 721.69),\n",
       " ('Union City', 'Hydration Packs', 296.91),\n",
       " ('Union City', 'Jerseys', 1597.49),\n",
       " ('Union City', 'Pedals', 490.73),\n",
       " ('Union City', 'Road Bikes', 53478.76),\n",
       " ('Union City', 'Road Frames', 10031.9),\n",
       " ('Union City', 'Socks', 5.39),\n",
       " ('Union City', 'Tires and Tubes', 8.22),\n",
       " ('Union City', 'Vests', 904.95),\n",
       " ('Woolston', 'Bike Racks', 432.0),\n",
       " ('Woolston', 'Bottles and Cages', 31.79),\n",
       " ('Woolston', 'Caps', 53.9),\n",
       " ('Woolston', 'Cleaners', 38.16),\n",
       " ('Woolston', 'Gloves', 161.59),\n",
       " ('Woolston', 'Helmets', 495.36),\n",
       " ('Woolston', 'Hydration Packs', 296.91),\n",
       " ('Woolston', 'Jerseys', 1178.02),\n",
       " ('Woolston', 'Saddles', 63.16),\n",
       " ('Woolston', 'Tires and Tubes', 10.96),\n",
       " ('Woolston', 'Touring Bikes', 77040.15),\n",
       " ('Woolston', 'Touring Frames', 9430.95),\n",
       " ('Woolston', 'Vests', 1108.19)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS ( -- the top three cities\n",
    "    SELECT Address.City, \n",
    "           SUM(SalesOrderHeader.SubTotal) subt FROM\n",
    "    Address JOIN SalesOrderHeader ON (\n",
    "          SalesOrderHeader.ShipToAddressID=Address.AddressID)\n",
    "    GROUP BY Address.City\n",
    "    ORDER BY subt DESC\n",
    "    LIMIT 3\n",
    ")\n",
    "SELECT Address.City, ProductCategory.Name Category,\n",
    "    ROUND(SUM(OrderQty*UnitPrice), 2) amount FROM\n",
    "      Address JOIN SalesOrderHeader ON (\n",
    "            SalesOrderHeader.ShipToAddressID=Address.AddressID) JOIN\n",
    "        SalesOrderDetail ON (\n",
    "            SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID) JOIN\n",
    "        Product ON (\n",
    "            Product.ProductID=SalesOrderDetail.ProductID) JOIN\n",
    "        ProductCategory ON (\n",
    "            Product.ProductCategoryID=ProductCategory.ProductCategoryID) JOIN\n",
    "        t ON (Address.City=t.City)\n",
    "    GROUP BY Address.City, ProductCategory.name\n",
    "    ORDER BY Address.City, ProductCategory.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
